import paramiko
import re
from typing import List, Dict, Optional
from commons.logger import log  # 请替换为你实际的日志模块名


class PostgresSSHOperator:
    def __init__(self, ssh_host: str, ssh_user: str, ssh_password: str):
        self.ssh_host = ssh_host
        self.ssh_user = ssh_user
        self.ssh_password = ssh_password
        self.conn_params = {
            'host': '127.0.0.1',
            'user': 'ugreen',
            'dbname': 'nasdb',
            'password': 'ugreen!@#$',
            'port': '5432',
            'sslmode': 'disable'
        }
        log.info(f"初始化 PostgresSSHOperator，目标主机: {self.ssh_host}，用户名: {self.ssh_user}")

    def _execute_psql_command(self, command: str) -> str:
        """通过SSH执行psql命令并返回结果"""
        ssh = paramiko.SSHClient()
        ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        log.info(f"开始执行 PSQL 命令: {command}，目标主机: {self.ssh_host}")
        try:
            # 建立SSH连接
            log.info(f"正在连接到 SSH 服务器 {self.ssh_host}，使用用户名 {self.ssh_user}")
            ssh.connect(
                self.ssh_host,
                username=self.ssh_user,
                password=self.ssh_password,
                timeout=10
            )
            log.info(f"成功连接到 SSH 服务器 {self.ssh_host}")

            # 构建PSQL连接字符串
            conn_str = (
                f"host={self.conn_params['host']} "
                f"user={self.conn_params['user']} "
                f"dbname={self.conn_params['dbname']} "
                f"password={self.conn_params['password']} "
                f"port={self.conn_params['port']} "
                f"sslmode={self.conn_params['sslmode']}"
            )

            # 执行组合命令
            full_cmd = f"psql -c '{command}' '{conn_str}'"
            log.info(f"执行完整命令: {full_cmd}")
            stdin, stdout, stderr = ssh.exec_command(full_cmd)

            # 获取输出并校验错误
            output = stdout.read().decode('utf-8').strip()
            error = stderr.read().decode('utf-8').strip()

            if error and 'ERROR' in error:
                log.error(f"PSQL执行错误: {error}，目标主机: {self.ssh_host}，命令: {command}")
                raise RuntimeError(f"PSQL执行错误: {error}")

            log.info(f"PSQL 命令执行成功，输出: {output}")
            return output

        except paramiko.AuthenticationException:
            log.error(f"SSH认证失败，请检查用户名密码，目标主机: {self.ssh_host}，用户名: {self.ssh_user}")
            raise RuntimeError("SSH认证失败，请检查用户名密码")
        except paramiko.SSHException as e:
            log.error(f"SSH连接异常: {str(e)}，目标主机: {self.ssh_host}，用户名: {self.ssh_user}")
            raise RuntimeError(f"SSH连接异常: {str(e)}")
        finally:
            log.info(f"关闭 SSH 连接，目标主机: {self.ssh_host}")
            ssh.close()

    def get_table_structure(self) -> List[Dict[str, str]]:
        """
        获取表结构数据
        返回示例: [{'schema': 'public', 'name': 'ddns', 'type': 'table', 'owner': 'ugreen'}, ...]
        """
        log.info(f"开始获取表结构数据，目标主机: {self.ssh_host}")
        output = self._execute_psql_command('\dt')
        try:
            result = self._parse_table_list(output)
            log.info(f"成功获取表结构数据，结果: {result}")
            return result
        except ValueError as e:
            log.error(f"解析表结构输出失败: {e}，目标主机: {self.ssh_host}")
            raise

    def _parse_table_list(self, output: str) -> List[Dict[str, str]]:
        """解析\dt命令输出"""
        log.info(f"开始解析表结构输出，目标主机: {self.ssh_host}")
        tables = []
        lines = output.split('\n')

        # 定位数据起始行
        start_index = next(
            (i for i, line in enumerate(lines) if line.startswith('--------')),
            None
        )

        if not start_index:
            log.error(f"无法解析表结构输出，目标主机: {self.ssh_host}")
            raise ValueError("无法解析表结构输出")

        # 提取数据行
        for line in lines[start_index + 1:]:
            if not line.strip():
                continue

            # 使用正则表达式匹配表结构行
            match = re.match(
                r'^(\w+)\s+\| (\w+)\s+\| (\w+)\s+\| (\w+)$',
                line.strip()
            )
            if match:
                tables.append({
                    'schema': match.group(1),
                    'name': match.group(2),
                    'type': match.group(3),
                    'owner': match.group(4)
                })

        log.info(f"成功解析表结构输出，结果: {tables}，目标主机: {self.ssh_host}")
        return tables

    def get_ddns_data(self) -> List[Dict[str, str]]:
        """获取ddns表数据"""
        log.info(f"开始获取 ddns 表数据，目标主机: {self.ssh_host}")
        output = self._execute_psql_command('SELECT * FROM ddns;')
        try:
            result = self._parse_ddns_output(output)
            log.info(f"成功获取 ddns 表数据，结果: {result}，目标主机: {self.ssh_host}")
            return result
        except ValueError as e:
            log.error(f"解析 ddns 表输出失败: {e}，目标主机: {self.ssh_host}")
            raise

    def _parse_ddns_output(self, output: str) -> List[Dict[str, str]]:
        """解析SELECT输出"""
        log.info(f"开始解析 ddns 表 SELECT 输出，目标主机: {self.ssh_host}")
        lines = output.split('\n')
        result = []

        # 定位数据起始行
        header_index = next(
            (i for i, line in enumerate(lines) if line.startswith('----')),
            None
        )

        if not header_index or header_index < 1:
            log.error(f"无法解析数据表输出，目标主机: {self.ssh_host}")
            raise ValueError("无法解析数据表输出")

        # 提取字段名
        headers = [h.strip() for h in re.split(r'\s+\|', lines[header_index - 1])]

        # 处理数据行
        for line in lines[header_index + 1:]:
            if not line.strip() or line.startswith('('):
                continue

            # 分割数据列
            columns = [col.strip() for col in re.split(r'\s+\|', line)]
            if len(columns) != len(headers):
                continue

            # 构建记录字典
            record = {
                'service_provider': columns[headers.index('service_provider')],
                'domain': columns[headers.index('domain')],
                'access_id': columns[headers.index('access_id')],
                'access_key': columns[headers.index('access_key')]
            }
            result.append(record)

        log.info(f"成功解析 ddns 表 SELECT 输出，结果: {result}，目标主机: {self.ssh_host}")
        return result


# 使用示例
if __name__ == "__main__":
    operator = PostgresSSHOperator(
        ssh_host="172.17.20.123",
        ssh_user="root",
        ssh_password="Aa123456@"
    )

    try:
        log.info("开始执行主程序，检查 ddns 表是否存在并获取数据")
        # 检查表是否存在
        tables = operator.get_table_structure()
        ddns_exists = any(t['name'] == 'ddns' for t in tables)

        if not ddns_exists:
            log.info("ddns表不存在")
            print("ddns表不存在")
            exit()

        # 获取数据
        data = operator.get_ddns_data()
        log.info(f"获取到的DDNS配置: {data}")
        print(f"获取到的DDNS配置:{data}")
        for item in data:
            print(f"- 服务商: {item['service_provider']}")
            print(f"  域名: {item['domain']}")
            print(f"  AccessID: {item['access_id'][:6]}***")  # 部分隐藏敏感信息
            print(f"  AccessKey: {item['access_key'][:6]}***")
        log.info("主程序执行完毕")

    except Exception as e:
        log.error(f"操作失败: {str(e)}")
        print(f"操作失败: {str(e)}")
